#!/usr/bin/env python3
import sys
from database import init_db, SessionLocal, DBUser, DBProduct, engine, Base
from sqlalchemy import text

def check_database_status():
    print("=" * 50)
    print("数据库状态检查")
    print("=" * 50)
    
    try:
        db = SessionLocal()
        
        user_count = db.query(DBUser).count()
        print(f"✓ 用户表: {user_count} 条记录")
        
        product_count = db.query(DBProduct).count()
        print(f"✓ 商品数据表: {product_count} 条记录")
        
        if user_count > 0:
            print("\n用户列表:")
            users = db.query(DBUser).all()
            for user in users:
                print(f"  - {user.username} ({user.name}) - {user.role.value}")
        
        if product_count > 0:
            print("\n最近5条商品数据:")
            recent_products = db.query(DBProduct).order_by(DBProduct.created_at.desc()).limit(5).all()
            for product in recent_products:
                print(f"  - {product.product_name} ({product.category}): ¥{product.price} 库存:{product.stock} 销量:{product.sales}")
        
        db.close()
        
    except Exception as e:
        print(f"✗ 检查数据库状态时出错: {e}")

def reset_database():
    print("=" * 50)
    print("重置数据库")
    print("=" * 50)
    
    try:
        print("正在删除所有表...")
        Base.metadata.drop_all(bind=engine)
        print("✓ 所有表已删除")
        print("开始重新初始化数据库...")
        init_db()
        
    except Exception as e:
        print(f"✗ 重置数据库时出错: {e}")

def clear_product_data():
    print("=" * 50)
    print("清空商品数据")
    print("=" * 50)
    
    try:
        db = SessionLocal()
        count = db.query(DBProduct).count()
        
        if count == 0:
            print("商品数据表已经是空的")
            return
        
        db.query(DBProduct).delete()
        db.commit()
        print(f"✓ 已清空 {count} 条商品数据")
        db.close()
        
    except Exception as e:
        print(f"✗ 清空商品数据时出错: {e}")

def show_tables():
    print("=" * 50)
    print("数据库表结构")
    print("=" * 50)
    
    try:
        with engine.connect() as conn:
            result = conn.execute(text("SHOW TABLES"))
            tables = result.fetchall()
            
            print("数据库中的表:")
            for table in tables:
                table_name = table[0]
                print(f"\n表名: {table_name}")
                
                result = conn.execute(text(f"DESCRIBE {table_name}"))
                columns = result.fetchall()
                
                print("  字段信息:")
                for col in columns:
                    print(f"    {col[0]} ({col[1]}) - {'主键' if col[3] == 'PRI' else ''}{'非空' if col[2] == 'NO' else '可空'}")
                
    except Exception as e:
        print(f"✗ 显示表结构时出错: {e}")

def main():
    if len(sys.argv) < 2:
        print("数据库管理工具")
        print("使用方法:")
        print("  python db_manager.py init     - 初始化数据库")
        print("  python db_manager.py status   - 检查数据库状态")
        print("  python db_manager.py reset    - 重置数据库")
        print("  python db_manager.py clear    - 清空商品数据")
        print("  python db_manager.py tables   - 显示表结构")
        return
    
    command = sys.argv[1].lower()
    
    if command == "init":
        init_db()
    elif command == "status":
        check_database_status()
    elif command == "reset":
        confirm = input("确认要重置数据库吗？这将删除所有数据！(y/N): ")
        if confirm.lower() == 'y':
            reset_database()
        else:
            print("操作已取消")
    elif command == "clear":
        confirm = input("确认要清空商品数据吗？(y/N): ")
        if confirm.lower() == 'y':
            clear_product_data()
        else:
            print("操作已取消")
    elif command == "tables":
        show_tables()
    else:
        print(f"未知命令: {command}")

if __name__ == "__main__":
    main()